﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using VeMayBayDTO;

namespace VeMayBayDAO
{
    public class TraCuuChuyenBayDAO
    {
        public static DataTable TimChuyenBay(HanhTrinhDTO ht, DateTime ngaygio)
        {
            DataTable dt = new DataTable();

            string query = @"select cb.machuyenbay ,cb.tenMaChuyenBay,cb.ngayGio
                            from hanhtrinh ht , lichchuyenbay cb
                            where cb.ngayGio = '" + ngaygio.ToShortDateString() +  "'and ht.mahanhtrinh = cb.mahanhtrinh and ht.sanbaydi =" + ht.SanBayDi + " and ht.sanbayden = " + ht.SanBayDen + "";
            dt = DataProvider.ExecQuery(query);            	 
            return dt;
        }

        public static DataTable TraCuuChuyenBay(ChuyenBayCtr dieukien)
        {
            SqlConnection cn = new SqlConnection();
            cn = DataProvider.con;
            DataProvider.Connect();

            SqlCommand cmd = TaoCauTruyVan(dieukien);
            cmd.Connection = cn;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            DataProvider.DisConnect();
            return dt;
        }

        public static SqlCommand TaoCauTruyVan(ChuyenBayCtr dieuKien)
        {
            SqlCommand cmd = new SqlCommand();

            string dkSBDi = " 1=1 ";
            string dkSBDen = " 1=1 ";
            string dkNgayBay = " 1=1 ";
            string dkThoiGianBay = " 1=1 ";
            string dkHangVe = " 1=1 ";
           

            if (dieuKien.IsSbDi)
            {
                dkSBDi = " HT.sanbaydi = @sanbaydi ";
                //cmd.Parameters.Add("@MSSV", SqlDbType.Char);
                //cmd.Parameters["@MSSV"].Value = "%" + dieuKien.MSSV + "%";
                SqlParameter prm = DataProvider.CreateParamater("@sanbaydi", dieuKien.SbDi.ToString(), SqlDbType.Int);
                cmd.Parameters.Add(prm);
            }

            if (dieuKien.IsSbDen)
            {
                dkSBDen = " HT.SanbayDen = @sanbayden ";
                SqlParameter prm = DataProvider.CreateParamater("@sanbayden", dieuKien.SbDen.ToString(), SqlDbType.Int);
                cmd.Parameters.Add(prm);
            }

           
            if (dieuKien.IsNgayBay)
            {
                dkNgayBay = " CB.NgayGio BETWEEN @CanDuoiNB AND @CanTrenNB ";
                DateTime canDuoiNB = new DateTime();
                canDuoiNB = dieuKien.CanDuoiNgayBay;
                DateTime canTrenNB = new DateTime();
                canTrenNB = dieuKien.CanTrenNgayBay;
                //cmd.Parameters.Add("@CanDuoiNS", SqlDbType.VarChar);
                //cmd.Parameters["@CanDuoiNS"].Value = canDuoiNS.ToString();
                SqlParameter prm = DataProvider.CreateParamater("@CanDuoiNB", canDuoiNB.ToShortDateString(), SqlDbType.VarChar);
                cmd.Parameters.Add(prm);
                //cmd.Parameters.Add("@CanTrenNS", SqlDbType.VarChar);
                //cmd.Parameters["@CanTrenNS"].Value = canTrenNS.ToString();
                prm = DataProvider.CreateParamater("@CanTrenNB", canTrenNB.ToShortDateString(), SqlDbType.VarChar);
                cmd.Parameters.Add(prm);
            }

            
          
            if (dieuKien.IsThoiGianBay)
            {
                dkThoiGianBay = " CB.ThoiGianBay BETWEEN @CanDuoiTGB AND @CanTrenTGB ";
                //cmd.Parameters.Add("@CanDuoiDTH", SqlDbType.Decimal);
                //cmd.Parameters["@CanDuoiDTH"].Value = dieuKien.CanDuoiDTH;
                SqlParameter prm = DataProvider.CreateParamater("@CanDuoiTGB", dieuKien.CanDuoiTGBay.ToString(), SqlDbType.Int);
                cmd.Parameters.Add(prm);
                //cmd.Parameters.Add("@CanTrenDTH", SqlDbType.Decimal);
                //cmd.Parameters["@CanTrenDTH"].Value = dieuKien.CanTrenDTH;
                prm = DataProvider.CreateParamater("@CanTrenTGB", dieuKien.CanTrenTGBay.ToString(), SqlDbType.Int);
                cmd.Parameters.Add(prm);
            }

            if (dieuKien.IsHangVe)
            {
                dkHangVe = " DG.MaHangVe = @MaHangVe ";
                //cmd.Parameters.Add("@CanDuoiDTB", SqlDbType.Decimal);
                //cmd.Parameters["@CanDuoiDTB"].Value = dieuKien.CanDuoiDTB;
                SqlParameter prm = DataProvider.CreateParamater("@MaHangVe", dieuKien.HangVe.ToString(), SqlDbType.Int);
                

                cmd.Parameters.Add(prm);
            }

          
            string sql = "SELECT cb.TenMaChuyenBay,CB.NgayGio,CB.ThoiGianBay , hv.TenHangVe, DG.GiaTien ";
            sql += "FROM LichChuyenBay CB, HanhTrinh HT, DonGia DG , HangVe hv ";
            sql += " WHERE ";
            sql += dkSBDi + " AND ";
            sql += dkSBDen + " AND ";
            sql += dkNgayBay + " AND ";
            sql += dkThoiGianBay + " AND ";
            sql += dkHangVe + " AND ";
            sql += " CB.MaHanhTrinh = HT.MaHanhTrinh AND HT.MaHanhTrinh = DG.MaHanhTrinh and dg.mahangve = hv.mahangve ";
            cmd.CommandText = sql;
            //
            //List<SqlParameter> SqlParams = new List<SqlParameter>();
            //foreach (SqlParameter prm in cmd.Parameters)
            //{
            //    SqlParams.Add(prm);
            //}

            //return DataProvider.ExecQuery(cmd.CommandText, SqlParams);

            return cmd;

        }
    }
}
